﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
namespace Common.DBUtility
{
    

    public static class SqlConnectionExtension
    {
        /// <summary>
        /// 批量添加datatable
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public static bool Insert(DataTable data)
        {
            try
            {
                //连接sql数据库语句
                using (SqlConnection conn = new SqlConnection("data source=.;initial catalog=YzSystem;persist security info=True;user id=sa;password=xxx;"))
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(conn))
                    {


                        //     列映射集合。
                        sqlBulkCopy.ColumnMappings.Add(0, "ID");
                        sqlBulkCopy.ColumnMappings.Add(1, "EvaluatorID");
                        sqlBulkCopy.ColumnMappings.Add(2, "CriticID");
                        sqlBulkCopy.ColumnMappings.Add(3, "IsEvaluated");
                        sqlBulkCopy.ColumnMappings.Add(4, "Weight");
                        sqlBulkCopy.ColumnMappings.Add(5, "Year");
                        sqlBulkCopy.ColumnMappings.Add(6, "EvaluationInfoID");
                        sqlBulkCopy.ColumnMappings.Add(7, "IsUsed");

                        //每一批次中的行数。在每一批次结束时，将该批次中的行发送到服务器。
                        sqlBulkCopy.BatchSize = data.Rows.Count;

                        //超时之前操作完成所允许的秒数。
                        sqlBulkCopy.BulkCopyTimeout = 60;
                        //服务器上目标表的名称。
                        sqlBulkCopy.DestinationTableName = "YzSettingEvaluationEntity";

                        //将data这个datatable中的表复制到目标表中。
                        sqlBulkCopy.WriteToServer(data);
                    }
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    return true;
                }
            }
            catch (Exception ex)
            {

                return false;
            }

        }

        /// <summary>
        /// 扩展表
        /// </summary>
        /// <param name="sqlBulkCopy"></param>
        static void GetProjectItemExtend(SqlBulkCopy sqlBulkCopy)
        {
            sqlBulkCopy.ColumnMappings.Add(0, "itemId");
            sqlBulkCopy.ColumnMappings.Add(1, "itemParentId");
            sqlBulkCopy.ColumnMappings.Add(2, "projectId");
            sqlBulkCopy.ColumnMappings.Add(3, "countReported");
            sqlBulkCopy.ColumnMappings.Add(4, "countInitial");
            sqlBulkCopy.ColumnMappings.Add(5, "totalPriceReported");
            sqlBulkCopy.ColumnMappings.Add(6, "totalPriceInital");
            sqlBulkCopy.ColumnMappings.Add(7, "budgetWorkPrice");
            sqlBulkCopy.ColumnMappings.Add(8, "budgetCommonPrice");
            sqlBulkCopy.ColumnMappings.Add(9, "budgetMaterialPrice");
            sqlBulkCopy.ColumnMappings.Add(10, "outputValue");
            sqlBulkCopy.ColumnMappings.Add(11, "grossProfit");
            sqlBulkCopy.ColumnMappings.Add(12, "cost");
            sqlBulkCopy.ColumnMappings.Add(13, "forecastCost");
            sqlBulkCopy.ColumnMappings.Add(14, "forecastGrossProfit");
            sqlBulkCopy.ColumnMappings.Add(15, "updateTime");
        }
        /// <summary>
        /// 物资材料
        /// </summary>
        /// <param name="sqlBulkCopy"></param>
        static void GetProjectMaterialItem(SqlBulkCopy sqlBulkCopy)
        {
            sqlBulkCopy.ColumnMappings.Add(0, "id");
            sqlBulkCopy.ColumnMappings.Add(1, "projectId");
            sqlBulkCopy.ColumnMappings.Add(2, "projectItemId");
            sqlBulkCopy.ColumnMappings.Add(3, "materialId");
            sqlBulkCopy.ColumnMappings.Add(4, "type");
            sqlBulkCopy.ColumnMappings.Add(5, "title");
            sqlBulkCopy.ColumnMappings.Add(6, "count");
            sqlBulkCopy.ColumnMappings.Add(7, "itemActualCount");
            sqlBulkCopy.ColumnMappings.Add(8, "unitPrice");
            sqlBulkCopy.ColumnMappings.Add(9, "unitName");
            sqlBulkCopy.ColumnMappings.Add(10, "totalPrice");
            sqlBulkCopy.ColumnMappings.Add(11, "addTime");
            sqlBulkCopy.ColumnMappings.Add(12, "updateTime");
            sqlBulkCopy.ColumnMappings.Add(13, "isDeleted");
        }
        /// <summary>
        /// 定额项目
        /// </summary>
        /// <param name="sqlBulkCopy"></param>
        static void GetProjectItemQuota(SqlBulkCopy sqlBulkCopy)
        {
            sqlBulkCopy.ColumnMappings.Add(0, "id");
            sqlBulkCopy.ColumnMappings.Add(1, "projectItemId");
            sqlBulkCopy.ColumnMappings.Add(2, "name");
            sqlBulkCopy.ColumnMappings.Add(3, "code");
            sqlBulkCopy.ColumnMappings.Add(4, "unitName");
            sqlBulkCopy.ColumnMappings.Add(5, "count");
            sqlBulkCopy.ColumnMappings.Add(6, "laborUnitPrice");
            sqlBulkCopy.ColumnMappings.Add(7, "materialUnitPrice");
            sqlBulkCopy.ColumnMappings.Add(8, "machineUnitPrice");
            sqlBulkCopy.ColumnMappings.Add(9, "manageUnitPrice");
            sqlBulkCopy.ColumnMappings.Add(10, "addTime");
        }

    }
}
